* Crosswalk 5-digit zip codes to CFIPS

local file_list : dir "${raw_data}/crosswalks/geography/zip_x_county" files "*.xlsx"

foreach x of local file_list {

	import excel "${raw_data}/crosswalks/geography/zip_x_county/`x'", firstrow case(lower) clear

	rename zip zipfive

	gsort zipfive -tot_ratio

	collapse (first) county tot_ratio, by(zipfive)

	keep zipfive county

	sort zipfive county

	rename county cfips 
	
	gen year	= substr("`x'", 14, 4)
	gen month	= substr("`x'", 12, 2)

	qui drop if mi(zipfive)
	
	qui tempfile _`=substr("`x'", 1, 17)'
	qui save `"`_`=substr("`x'", 1, 17)''"'
	
	
}

foreach x of local file_list {

	append using `"`_`=substr("`x'", 1, 17)''"'
}

duplicates drop zipfive cfips, force

sort zipfive year

bys zipfive: gen id = _n

drop if id > 1

drop year month id

rename cfips zcfips 

save "${intermediate_data}/crosswalks/zip_to_cfips_xwalk.dta", replace


* Crosswalk county FIPS to CZ codes (CZ codes 1980, 1990, 2000)

import excel "${raw_data}/crosswalks/geography/czlma903.xls", sheet("CZLMA903") firstrow case(lower) clear

keep countyfipscode countyname cz90 cz80 

rename countyfipscode cfips

* manually add counties that don't appear in the crosswalks due to county merges/recodes
assert !mi(cfips)
foreach c in "02105" "02158" "02195" "02198" "02230" "02275" "02282" "08014" "12086" "46102"{
	insobs 1 
	replace cfips = "`c'" if mi(cfips)
}
replace cz90 = "34109" if cfips == "02105" & mi(cz90)   // Hoonah-Angoon Census Area, AK
replace cz90 = "34112" if cfips == "02158" & mi(cz90)   // Kusilvak Census Area, AK
replace cz90 = "34111" if cfips == "02195" & mi(cz90)   // Petersburg Borough, AK
replace cz90 = "34110" if cfips == "02198" & mi(cz90)   // Prince of Wales-Hyder Census Area, AK
replace cz90 = "34109" if cfips == "02230" & mi(cz90)   // Skagway Municipality, AK
replace cz90 = "34110" if cfips == "02275" & mi(cz90)   // Wrangell City and Borough, AK
replace cz90 = "34109" if cfips == "02282" & mi(cz90)   // Yakutat City and Borough, AK
replace cz90 = "28900" if cfips == "08014" & mi(cz90)   // Broomfield County, CO
replace cz90 = "07000" if cfips == "12086" & mi(cz90)   // Miami-Dade County, FL
replace cz90 = "27704" if cfips == "46102" & mi(cz90)   // Oglala Lakota County, SD

gen stfips = substr(cfips, 1, 2)

tempfile cz80_90
save "`cz80_90'"

import excel "${raw_data}/crosswalks/geography/cz00_eqv_v1.xls", sheet("CZ00_Equiv") firstrow case(lower) clear

keep fips commutingzoneid2000

rename (fips commutingzoneid2000) (cfips cz00)

merge 1:1 cfips using "`cz80_90'", keep(match using) nogen 

tempfile cz80_90_00
save "`cz80_90_00'"

* Crosswalk state FIPS codes to state names

import delimited "${raw_data}/crosswalks/geography/us-state-ansi-fips.csv", clear 

rename (stname st stusps) (state_name stfips state)

tostring stfips, format(%02.0f) replace

foreach x of varlist _all {
	replace `x' = strtrim(`x')
}

isid stfips

save "${intermediate_data}/crosswalks/statefips_x_stateabbrev.dta", replace

merge 1:m stfips using "`cz80_90_00'", assert(matched) nogen

* Crosswalk CFIPS to CBSA and MSA codes

preserve

import excel "${raw_data}/crosswalks/geography/list1.xls", sheet("List 1") cellrange(A3:L1888) firstrow case(lower) clear

drop if mi(metropolitanmicropolitanstatis)

compress

save "${intermediate_data}/crosswalks/cbsa2fipsxw.dta", replace

restore

order cfips countyname cz80 cz90 cz00 stfips state_name state

sort cfips countyname cz80 cz90 cz00 stfips state_name state

isid cfips

save "${intermediate_data}/crosswalks/cfips_cz_state_xwalk.dta", replace
